using System;
using Microsoft.Office.Interop.Excel;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.IO;
using System.Data;

namespace Dll.DAL
{
  public  class ExcelHelper
    {

      private static string GetStringWithoutChar(string s, char c)
      {
          string temp = "";
          for (int i = 0; i < s.Length; i++)
          {
              if (s[i] != c)
                  temp += s[i];
          }
          return temp;
      }
     
      public static List<string> GetSheets(string filename)
      {
          List<string> SheetList = new List<string>();
       /*   Application app = new Application();
          Workbook wd = app.Workbooks.Open(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

         

          for (int i = 0; i < wd.Sheets.Count; i++)
          {
              SheetList.Add(((Worksheet)wd.Sheets[i+1]).Name);
          }
          
          wd.Close(false,null,null);
          app.Quit();*/
          OleDbConnection connect = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+filename+";Extended Properties='Excel 5.0;HDR=Yes;IMEX=1';");

          connect.Open();
          System.Data.DataTable dt = connect.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[4] { null, null, null, "TABLE" });
          connect.Close();
          DataTableReader dtr = dt.CreateDataReader();
          while (dtr.Read())
          {
              string s = "";
              s = dtr.GetValue(2).ToString();
              s = GetStringWithoutChar(s, '\'');
              if(s.Length!=0)
              {
                 if(s[s.Length-1]=='$')
                     SheetList.Add(s);
              }
          }

          return SheetList;
      }

      public static List<string> GetColumnName(string filename, string SheetName,int RowNum)
      {
          OleDbConnection connect = new OleDbConnection();
          System.Data.DataTable dt = new System.Data.DataTable();
          dt.Clear();
          OleDbCommand command = new OleDbCommand();
          FileInfo file = new FileInfo(filename);
          if (file.Extension.ToLower().IndexOf("xls") != -1)
          {
              SheetName += "$";
              connect.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filename;
              connect.ConnectionString += ";Extended Properties=\"Excel 8.0;\"";
              command.CommandText = "Select  * from [" + SheetName + "A" + RowNum.ToString() + ":IV15000] where 1=2";
              //command.CommandText = "Select  * from [" + SheetName + "] where 1=2";
          }
          else
          {
              connect.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + file.Directory;
              connect.ConnectionString += ";Extended Properties=dBASE IV";
              command.CommandText = "Select  * from [" + SheetName+"] where 1=2";
          }
          command.Connection = connect;
          List<string> Columns = new List<string>();
          OleDbDataAdapter adapter = new OleDbDataAdapter(command);
          try
          {
              adapter.Fill(dt);
              
              for (int i = 0; i < dt.Columns.Count; i++)
              {
                  Columns.Add(new string(dt.Columns[i].ColumnName.ToCharArray()));
              }
          }
          catch { }
          connect.Close();
          return Columns;
      }
    }
}
